package com.dy.yunying.biz.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dy.yunying.api.entity.ExcelData;
import com.dy.yunying.api.entity.gametask.ExportGameTaskConfig;
import com.pig4cloud.pig.api.util.StringUtils;
import com.pig4cloud.pig.common.core.util.R;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author cx
 * @version 1.0.0
 * @ClassName Export.java
 * @createTime 2021年03月11日 17:47:00
 */
public class ExportUtils {

	/**
	 * 为要导出的数据值拼接后缀，如果值为空，则填充默认值
	 *
	 * @param listMap
	 * @param suffix
	 * @param defaultValue
	 * @param columnNames
	 */
	public static void spliceSuffix(List<Map<String, Object>> listMap, String suffix, Object defaultValue, String... columnNames) {
		if (null == columnNames || 0 == columnNames.length) {
			return;
		}
		Set<String> columnNameSet = Arrays.stream(columnNames).collect(Collectors.toSet());
		for (Map<String, Object> map : listMap) {
			for (Map.Entry<String, Object> entry : map.entrySet()) {
				if (columnNameSet.contains(entry.getKey())) {
					if (Objects.nonNull(entry.getValue())) {
						map.put(entry.getKey(), entry.getValue() + suffix);
					} else if (null != defaultValue) {
						map.put(entry.getKey(), defaultValue);
					}
				}
			}
		}
	}

	public static R exportExcelData(HttpServletRequest request,HttpServletResponse response, String fileName, String sheetName, String titles, String columns, List<Map<String,Object>> listMap){
		try {
			if (StringUtils.isBlank(sheetName)
					|| StringUtils.isBlank(sheetName)
					|| StringUtils.isBlank(sheetName)
					|| Objects.isNull(listMap) || listMap.size() == 0){
				return R.failed("导出:参数异常");
			}
			ExcelData data = new ExcelData();
			// sheet 表名称
			data.setName(sheetName);
			//表头
			List<String> titleList = new ArrayList();
			String[] title = titles.split(",");
			for (int i = 0; i < title.length; i++) {
				String name = title[i];
				titleList.add(name);
			}
			data.setTitles(titleList);

			String[] column = columns.split(",");
			//数据
			List<List<Object>> rows = new ArrayList();
			for (Map<String,Object> map : listMap){
				List<Object> row = new ArrayList();
				for (int i = 0; i < column.length; i++) {
					String key = column[i];
					row.add(map.get(key));
				}
				rows.add(row);
			}
			data.setRows(rows);
			// 导出
			exportExcel(request, response, fileName, data);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return R.ok();
	}
	public static R exportExcelData(HttpServletRequest request, HttpServletResponse response, String fileName, String sheetName, String titles, String columns, JSONArray list){
		try {
			if (StringUtils.isBlank(sheetName)
					|| StringUtils.isBlank(sheetName)
					|| StringUtils.isBlank(sheetName)
					|| Objects.isNull(list) || list.size() == 0){
				return R.failed("导出:参数异常");
			}
			ExcelData data = new ExcelData();
			// sheet 表名称
			data.setName(sheetName);
			//表头
			List<String> titleList = new ArrayList();
			String[] title = titles.split(",");
			for (int i = 0; i < title.length; i++) {
				String name = title[i];
				titleList.add(name);
			}
			data.setTitles(titleList);

			String[] column = columns.split(",");
			//数据
			List<List<Object>> rows = new ArrayList();

			// 计数
			Iterator<Object> iterator = list.iterator();
			// 判断是否存在下一组数据进行循环
			while (iterator.hasNext()){
				// 接收子数据，这里就是处理你需要操作的业务数据
				JSONObject jsonObject = (JSONObject) iterator.next();

				List<Object> row = new ArrayList();
				for (int i = 0; i < column.length; i++) {
					String key = column[i];
					row.add(jsonObject.get(key));
				}
				rows.add(row);
			}

			data.setRows(rows);
			// 导出
			exportExcel(request, response, fileName, data);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return R.ok();
	}

    /**
     * 下载文件Excel
	 * @param request
     * @param response
     * @param fileName
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletRequest request,HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
		String fileNameUtf8 = URLEncoder.encode(fileName, "UTF8");
//		String userAgent = request.getHeader("USER-AGENT");
//		String fileNameUtf8 = URLEncoder.encode(fileName, "UTF8");
//		if (StringUtils.isNotBlank(userAgent)) {
//			userAgent = userAgent.toLowerCase();
//			// IE浏览器，只能采用URLEncoder编码
//			if (userAgent.indexOf("msie") != -1)
//			{
//				fileName = "filename=\"" + fileName + "\"";
//			}
//			// Chrome浏览器，只能采用MimeUtility编码或ISO编码的中文输出
//			else if (userAgent.indexOf("applewebkit") != -1 )
//			{
//				fileNameUtf8 = MimeUtility.encodeText(fileName, "UTF8", "B");
//				fileName = "filename=\"" + fileNameUtf8 + "\"";
//			}
//			// FireFox浏览器，可以使用MimeUtility或filename*或ISO编码的中文输出
//			else if (userAgent.indexOf("mozilla") != -1)
//			{
//				fileNameUtf8 = MimeUtility.encodeText(fileName, "UTF8", "B");
//				fileName = "filename=\"" + fileNameUtf8 + "\"";
////				fileName = "filename*=UTF-8''" + fileNameUtf8;
//			}
//			// Opera浏览器只能采用filename*
//			else if (userAgent.indexOf("opera") != -1)
//			{
//				fileName = "filename*=UTF-8''" + fileNameUtf8;
//			}
//			// Safari浏览器，只能采用ISO编码的中文输出
//			else if (userAgent.indexOf("safari") != -1 )
//			{
//				fileName = "filename=\"" + new String(fileName.getBytes("UTF-8"),"ISO8859-1") + "\"";
//			}
//		}
//        response.setHeader("Content-Disposition", String.format("attachment; %s", fileName));
		response.setHeader("Content-Disposition", "attachment;filename=" + fileNameUtf8);
		response.setHeader("file-name", fileNameUtf8);
        exportExcel(data, response.getOutputStream());
    }

    /**
     * 创建 表格
     * @param data
     * @param out
     * @throws Exception
     */
    public static void exportExcel(ExcelData data, OutputStream out) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);
            wb.write(out);
        } finally {
           // wb.close();
        }
    }

    /**
     * 将数据写入表格
     * @param wb
     * @param sheet
     * @param data
     */
    private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
        int rowIndex = 0;
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
        writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
        autoSizeColumns(sheet, data.getTitles().size() + 1);
    }

    /**
     * 写入表头
     * @param wb
     * @param sheet
     * @param titles
     * @return
     */
    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;
        //获取字体
        Font titleFont = wb.createFont();
        //设置字体名称（宋体）
        titleFont.setFontName("simsun");
        //设置字体颜色 黑色
        titleFont.setColor(IndexedColors.BLACK.index);
        //获取单元格样式
        XSSFCellStyle titleStyle = wb.createCellStyle();
        //设置单元格的水平对齐类型(这里是水平居中)
        titleStyle.setAlignment(HorizontalAlignment.LEFT);
        //设置单元格的垂直对齐类型（这里是居中）
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //指定图案和纯色单元格填充的单元格填充信息（实心前景）
        titleStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        //设置字体样式
        titleStyle.setFont(titleFont);
        //在该工作簿中创建第一行.
        Row titleRow = sheet.createRow(rowIndex);
        colIndex = 0;
        //循环创建列
        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }
        //将行数++ 返回用于下面添加数据
        rowIndex++;
        return rowIndex;
    }

    /**
     * 将数据写入
     * @param wb
     * @param sheet
     * @param rows
     * @param rowIndex
     * @return
     */
    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex = 0;
        /*Font dataFont = wb.createFont();//获取字体
        dataFont.setFontName("simsun");//设置字体名称（宋体）
        dataFont.setColor(IndexedColors.BLACK.index);//设置字体颜色 黑色
        XSSFCellStyle dataStyle = wb.createCellStyle();//获取单元格样式
        dataStyle.setAlignment(HorizontalAlignment.CENTER);//设置单元格的水平对齐类型(这里是水平居中)
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置单元格的垂直对齐类型（这里是居中）
        dataStyle.setFont(dataFont);//设置字体样式*/
        for (List<Object> rowData : rows) {//循环写入数据
            Row dataRow = sheet.createRow(rowIndex);
            colIndex = 0;
            for (Object cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cell.setCellValue(cellData.toString());
                } else {
                    cell.setCellValue("");
                }

                //cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    /**
     * 自动调整大小
     * @param sheet
     * @param columnNumber
     */
    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++) {
//            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
//            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
//            if (newWidth > orgWidth) {
//                sheet.setColumnWidth(i, newWidth);
//            } else {
//                sheet.setColumnWidth(i, orgWidth);
//            }
        }
    }

    /**
     * 设置表格样式
     * @param style
     * @param border
     * @param color
     */
    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }

    /**
     * 将rgb颜色码 转换为 XSSFColor
     * @param color
     * @return
     */
    /*private static XSSFColor createXssfColor(String color) {
        int[] rgbColor = hexToRgb(color);
        XSSFColor xssfColor = new XSSFColor(new java.awt.Color(rgbColor[0], rgbColor[1], rgbColor[2]), new DefaultIndexedColorMap());
        return xssfColor;
    }*/

    /**
     * 将颜色码 转换为 r g b
     * @param hex
     * @return
     */
    public static int[] hexToRgb(String hex) {
        String colorStr = hex;
        if (hex.startsWith("#")) {
            colorStr = hex.substring(1);
        }
        if (colorStr.length() == 8) {
            colorStr = hex.substring(2);
        }
        int  r=  Integer.valueOf( colorStr.substring( 0, 2 ), 16 );
        int  g=  Integer.valueOf( colorStr.substring( 2, 4 ), 16 );
        int  b=  Integer.valueOf( colorStr.substring( 4, 6 ), 16 );

        return new int[] { r, g, b };
    }

    /**
     * 导出txt文件
     * @param response
     * @param text
     */
    public static void exportTxt(HttpServletResponse response, String text){
        response.setCharacterEncoding("utf-8");
        //设置响应的内容类型
        response.setContentType("text/plain");
        //设置文件的名称和格式
        response.addHeader("Content-Disposition","attachment;filename="
                + genAttachmentFileName( "邀请码-" + DateUtils.getcurrentTime(), "邀请码")
                + ".txt");
        BufferedOutputStream buff = null;
        ServletOutputStream outStr = null;
        try {
            outStr = response.getOutputStream();
            buff = new BufferedOutputStream(outStr);
            buff.write(text.getBytes("UTF-8"));
            buff.flush();
            buff.close();
        } catch (Exception e) {
            //LOGGER.error("导出文件文件出错:{}",e);
        } finally {
            try {
                buff.close();
                outStr.close();
            } catch (Exception e) {
                //LOGGER.error("关闭流对象出错 e:{}",e);
            }
        }
    }
    //防止中文文件名显示出错
    public static String genAttachmentFileName(String cnName, String defaultName) {
        try {
            cnName = new String(cnName.getBytes("gb2312"), "ISO8859-1");
        } catch (Exception e) {
            cnName = defaultName;
        }
        return cnName;
    }

    public static void exportExcelTemplate(String fileName,String sheetName,Class clz,HttpServletResponse response ) throws IOException {
		OutputStream out = null;
    	try {
			response.addHeader("Content-Disposition", String.format("attachment;fileName=\"%s\"", new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1)));
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");


			out = response.getOutputStream();

			ExcelWriter excelWriter = EasyExcel.write(out, clz)
					.registerWriteHandler(getHorizontalCellStyleStrategy())
					.build();
			WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
			excelWriter.write(null, writeSheet);
			excelWriter.finish();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
    		if (out!=null){
				out.flush();
				out.close();
			}
		}
	}

	public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
		// 头的策略
		WriteCellStyle headWriteCellStyle = new WriteCellStyle();
		// 背景设置为白色
		headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		//边框
		headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
		headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
		headWriteCellStyle.setBorderRight(BorderStyle.THIN);
		headWriteCellStyle.setBorderTop(BorderStyle.THIN);
		//自动换行
		headWriteCellStyle.setWrapped(true);
		WriteFont headWriteFont = new WriteFont();
		headWriteFont.setBold(true);
		headWriteFont.setFontName("宋体");
		headWriteFont.setFontHeightInPoints((short)12);
		headWriteCellStyle.setWriteFont(headWriteFont);
		// 内容的策略
		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
		// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
		contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
		// 背景绿色
		contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		//边框
		contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
		contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
		contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
		contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
		//自动换行
		contentWriteCellStyle.setWrapped(true);
		//文字
		WriteFont contentWriteFont = new WriteFont();
		// 字体大小
		contentWriteFont.setFontHeightInPoints((short)12);
		contentWriteFont.setFontName("宋体");
		contentWriteCellStyle.setWriteFont(contentWriteFont);
		// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
		return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
	}


}
